﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;

namespace SqlAnalysis
{
    public class AnalysisTSQL
    {
        protected static Regex SqlMethodTagRegex = new Regex(@"(\(|\))", RegexOptions.Compiled);
        protected static Regex SqlMethodsRegex = new Regex(@"AVG\(.*\)|COUNT\(.*\)|MAX\(.*\)|MIN\(.*\)|SUM\(.*\)|ABS\(.*\)|COALESCE\(.*\)|IFNULL\(.*\)|LENGTH\(.*\)|LOWER\(.*\)|LTRIM\(.*\)|RANDOM\(.*\)|NULLIF\(.*\)|ROUND\(.*\)|RTRIM\(.*\)|SUBSTR\(.*\)|UPPER\(.*\)|TIME\(.*\)|STRFTIME\(.*\)", RegexOptions.Compiled);
        protected static Regex SqlSplitRegex = new Regex(@"(WHERE|INNER JOIN|LEFT JOIN|RIGHT JOIN|FULL JOIN|UNION ALL|UNION|JOIN|ORDER BY|GROUP BY| AND | OR | ON |\s)", RegexOptions.Compiled);
        protected static Regex WhereCharRegex = new Regex(@"^.*\s(NOT IN|NOT EXISTS|IN|EXISTS|LIKE|BETWEEN|IS|<|>|<=|>=|=|<>|!=)\s.*$", RegexOptions.Compiled);
        protected static Regex WhereCharMatchRegex = new Regex(@"(NOT IN|NOT EXISTS|IN|EXISTS|LIKE|BETWEEN|IS|<|>|<=|>=|=|<>|!=)", RegexOptions.Compiled);
        protected static Regex whereTagCharRegex = new Regex(@"(IS NULL|NOT IN|NOT EXISTS| IN | EXISTS | LIKE | BETWEEN | IS |<=|>=|=|<>|!=|<|>| AND | OR )", RegexOptions.Compiled);
        protected static Regex TagCharRegex = new Regex(@"(WHERE|INNER JOIN|LEFT JOIN|RIGHT JOIN|FULL JOIN|UNION ALL|UNION|JOIN|ORDER BY|GROUP BY|HAVING)", RegexOptions.Compiled);
        protected static Regex JoinCharRegex = new Regex(@"(INNER JOIN|LEFT JOIN|RIGHT JOIN|FULL JOIN|UNION ALL|UNION JOIN|UNION|JOIN)", RegexOptions.Compiled);
        protected static string[] JoinCharArray = "INNER JOIN|LEFT JOIN|RIGHT JOIN|FULL JOIN|UNION ALL|UNION JOIN|UNION|JOIN".Split('|');
        protected static string[] TagCharArray = "WHERE|INNER JOIN|LEFT JOIN|RIGHT JOIN|FULL JOIN|UNION ALL|UNION|JOIN|ORDER BY|GROUP BY|HAVING".Split('|');
        protected static string[] whereTagCharArray = "IS NULL|NOT IN|NOT EXISTS| IN | EXISTS | LIKE | BETWEEN | IS |<=|>=|=|<>|!=|<|>| AND | OR ".Split('|');


        protected Dictionary<string, string> Parameters { get; set; }


        protected string MatchCharParameter(string script)
        {
            Regex reg = new Regex("'(.+?)'");

            var result = reg.Matches(script);

            if (result.Count > 0)
            {
                this.Parameters = new Dictionary<string, string>();
            }

            foreach (Match match in result)
            {
                if (!this.Parameters.ContainsValue(match.Value))
                {
                    string tag = $"@param_{this.Parameters.Count}";
                    this.Parameters.Add(tag, match.Value);
                    script = script.Replace(match.Value, tag);
                }
            }

            script =
                script
                .ToUpper()
                .Replace("SELECT*", "SELECT * ")
                .Replace("*FROM", " * FROM ")
                .Replace("JOIN(", "JOIN (")
                .Replace("IN(", "IN (")
                .Replace("ON(", "ON (");

            return script;
        }


        protected string[] MatchFieldsByCondition(string[] condition)
        {
            condition = condition ?? new string[0];
            return condition.Count() == 0 ? condition : whereTagCharRegex.Split(string.Join(" ", condition))?.ToArray();
        }

        protected List<string> MatchFields(string[] fields)
        {
            List<string> concats = new List<string>();
            foreach (var field in fields)
            {
                float.TryParse(field, out float value);
                //if (whereTagCharRegex.IsMatch(field) || value != 0 || field.StartsWith("@PARAM_") || field.StartsWith("$SCOPE_"))
                if (whereTagCharArray.Contains(field) || (value != 0 || field.Trim() == "0") || field.StartsWith("@PARAM_") || field.StartsWith("$SCOPE_"))
                {
                    continue;
                }
                if (!concats.Contains(field)) concats.Add(field);
            }
            return concats;
        }

        protected void MatchCondition(string[] fields, List<ConditionGroup> wheres)
        {
            fields = fields.Select(item => item.Trim()).ToArray();

            var conditionGroup = new ConditionGroup();

            for (int i_fields = 0; i_fields < fields.Count(); i_fields++)
            {
                string leftField = fields[i_fields];
                bool isLeftScope = leftField.StartsWith("$SCOPE_");

                if (isLeftScope)
                {
                    Condition conditionOfScope = new Condition
                    {
                        IsLeftScope = isLeftScope,
                        LeftScope = leftField
                    };

                    string nextChar = fields.TryGet(i_fields + 1);
                    if (string.IsNullOrWhiteSpace(nextChar))
                        continue;

                    if (WhereCharRegex.IsMatch(nextChar))
                    {
                        string rightField = fields.TryGet(i_fields + 2);
                        bool isRightScope = rightField.StartsWith("$SCOPE_");
                        string[] rightFieldArray = isRightScope ? default : rightField.IndexOf('.') > -1 ? rightField.Split('.') : new string[] { rightField };
                        
                        conditionOfScope.Logic = $" {nextChar} ";
                        conditionOfScope.IsRightScope = isRightScope;
                        conditionOfScope.RightScope = rightField;
                        conditionOfScope.RightAlias = isRightScope ? string.Empty : rightFieldArray.Length == 1 ? string.Empty : rightFieldArray[0];
                        conditionOfScope.RightField = isRightScope ? string.Empty : rightFieldArray.Length == 1 ? rightFieldArray[0] : rightFieldArray[1];
                        conditionOfScope.Value = leftField + conditionOfScope.Logic + rightField;
                        i_fields += 2;
                    }

                    conditionGroup.Conditions.Add(conditionOfScope);
                }
                else
                {
                    string leftFN = leftField.IndexOf('`') > -1 ? leftField.Split('`')[0] : string.Empty;
                    leftField = leftFN == string.Empty ? leftField : leftField.Replace($"{leftFN}`",string.Empty).TrimEnd('`');
                    string[] leftFieldArray = leftField.IndexOf('.') > -1 ? leftField.Split('.') : new string[] { leftField };
                    string nextChar = fields.TryGet(i_fields + 1);
                    if (string.IsNullOrWhiteSpace(nextChar)) 
                        throw new Exception("parent sql error");

                    bool isBetween = nextChar == "BETWEEN";
                    nextChar = $" {nextChar} ";
                    string rightFN = string.Empty;
                    string rightField = fields.TryGet(i_fields + 2);
                    string right2Field = isBetween ? fields.TryGet(i_fields + 4) : string.Empty;

                    bool isRightScope = rightField.StartsWith("$SCOPE_");
                    if (!isRightScope)
                    {
                        rightFN = rightField.IndexOf('`') > -1 ? rightField.Split('`')[0] : string.Empty;
                        rightField = rightFN == string.Empty ? rightField : rightField.Replace($"{rightFN}`", string.Empty).TrimEnd('`'); 
                    }
                    string[] rightFieldArray = isRightScope ? default : rightField.IndexOf('.') > -1 ? rightField.Split('.') : new string[] { rightField };
                    string[] right2FieldArray = right2Field.IndexOf('.') > -1 ? right2Field.Split('.') : new string[] { right2Field };

                    conditionGroup.Conditions.Add(new Condition
                    {
                        IsLeftScope = isLeftScope,
                        LeftScope = string.Empty,
                        LeftFN = leftFN,
                        LeftAlias = leftFieldArray.Length == 1 ? string.Empty : leftFieldArray[0],
                        LeftField = leftFieldArray.Length == 1 ? leftFieldArray[0] : leftFieldArray[1],

                        Logic = nextChar,

                        IsRightScope = isRightScope,
                        RightScope = isRightScope ? rightField : string.Empty,
                        RightFN = rightFN,
                        RightAlias = isRightScope ? string.Empty : rightFieldArray.Length == 1 ? string.Empty : rightFieldArray[0],
                        RightField = isRightScope ? string.Empty : rightFieldArray.Length == 1 ? rightFieldArray[0] : rightFieldArray[1],

                        RightAlias2 = !isBetween ? string.Empty : right2FieldArray.Length == 1 ? string.Empty : right2FieldArray[0],
                        RightField2 = !isBetween ? string.Empty : right2FieldArray.Length == 1 ? right2FieldArray[0] : right2FieldArray[1],

                        Value = (leftFN == string.Empty ? leftField : $"{leftFN} ({leftField})") + nextChar + (rightFN == string.Empty ? rightField : $"{rightFN}({rightField})") + (isBetween ? $" {fields.TryGet(i_fields + 3)} {fields.TryGet(i_fields + 4)}" : string.Empty)
                    });

                    i_fields += 2;
                    if (isBetween)
                    {
                        i_fields += 2;
                    }
                }
                
                if (i_fields + 1 < fields.Length)
                {
                    string nextchar = fields.TryGet(i_fields + 1);
                    switch (nextchar)
                    {
                        case "AND":
                            break;
                        case "OR":
                            wheres.Add(conditionGroup);
                            conditionGroup = new ConditionGroup(); 
                            break;
                        default:
                            throw new Exception("parent sql error");
                    }
                    i_fields++;
                }
            }

            wheres.Add(conditionGroup);
        }

        // 处理多段 sql, by chat ;
        protected string[] SplitScript(string script)
        {
            var methods = SqlMethodsRegex.Matches(script);
            foreach (var item in methods)
            {
                string unitscope = item.ToString();
                script = script.Replace(unitscope, SqlMethodTagRegex.Replace(unitscope, "`"));
            }
            return script.Split(';');
        }

        // sql 分组, by ()
        protected Dictionary<int, StringBuilder> GroupBySqlItem(string sql)
        {
            int scpoCount = 0;
            Dictionary<int, StringBuilder> scpoScripts = new Dictionary<int, StringBuilder>();
            Dictionary<int, int> parentMaps = new Dictionary<int, int>();
            scpoScripts.Add(0, new StringBuilder());
            int position = 0;
            int scpos_position = 0;
            while (position < sql.Length)
            {
                if (string.Compare(sql, position, "(", 0, 1, true) == 0)
                {
                    scpoCount++;
                    scpoScripts.Add(scpoCount, new StringBuilder());
                    scpoScripts[scpos_position].Append($"$SCOPE_{scpoCount}");
                    if (!parentMaps.ContainsKey(scpoCount))
                    {
                        parentMaps.Add(scpoCount, scpos_position);
                    }
                    scpos_position++;
                }
                else if (string.Compare(sql, position, ")", 0, 1, true) == 0)
                {
                    //scpos_position--;
                    scpos_position = parentMaps[scpos_position];
                }
                else if (parentMaps.ContainsKey(scpos_position))
                {
                    var offset = parentMaps.Where(item => item.Value == parentMaps[scpos_position]).Max(item => item.Key);
                    scpoScripts[offset].Append(sql.Substring(position, 1));
                }
                else
                {
                    scpoScripts[scpos_position].Append(sql.Substring(position, 1));
                }

                position++;
            }
            return scpoScripts;
        }

        // Map Table 对象（name，as name）
        protected int MapExcuterTableInfo(OptionBase options, string[] splitArr, int index)
        {
            string name = splitArr[index];
            options.IsScope = name.StartsWith("$SCOPE_");
            options.TableName = name;
            
            //if (this.CheckArrLength(splitArr, index + 1) && !TagCharRegex.IsMatch(splitArr.TryGet(index + 1)))
            if (this.CheckArrLength(splitArr, index + 1) && !TagCharArray.Contains(splitArr.TryGet(index + 1)))
            {
                if (string.Compare(splitArr[index + 1], "as", true) == 0)
                {
                    if (this.CheckArrLength(splitArr, index + 2))
                    {
                        options.Alias = splitArr.TryGet(index + 2);
                        return 2;
                    }
                    else
                    {
                        throw new Exception($"sql error: {name} as null");
                    }
                }
                else
                {
                    options.Alias = splitArr.TryGet(index + 1);
                    return 1;
                }
            }
            else
            {
                return 0;
            }
        }



        protected FindTagRangeResult FindNextTagIndexRange(string[] splitArr, int startindex, string findTag = null)
        {
            if ((splitArr.Length - 1) == startindex)
            {
                return new FindTagRangeResult
                {
                    condition = new string[0]
                };
            }


            int nextTaglndex = this.FindNextTagIndex(splitArr, startindex);

            startindex = string.IsNullOrWhiteSpace(findTag) ? startindex : this.FindNextTagIndexByTag(splitArr, startindex, findTag);



            if (startindex == nextTaglndex && !string.IsNullOrWhiteSpace(findTag))
            {
                if (startindex == nextTaglndex && startindex == 0)
                {
                    return new FindTagRangeResult
                    {
                        condition = new string[0]
                    };
                }

                nextTaglndex = this.FindNextTagIndex(splitArr, ++startindex);
            }
            else if (!string.IsNullOrWhiteSpace(findTag))
            {
                return new FindTagRangeResult
                {
                    condition = new string[0]
                };
            }

            if (startindex <= (splitArr.Length - 1) & nextTaglndex == 0)
            {
                nextTaglndex = splitArr.Length;
            }

            if (nextTaglndex > 0)
            {
                var condition = splitArr.Skip(startindex).Take(nextTaglndex - startindex).ToArray(); 
                return new FindTagRangeResult
                {
                    condition = condition,
                    tag = splitArr.Length == nextTaglndex ? string.Empty : splitArr[nextTaglndex],
                    nextTagIndex = nextTaglndex
                };
            }
            else
            {
                return new FindTagRangeResult
                {
                    condition = new string[0]
                };
            }

        }

        // 从指定位置开始查找下一个tag
        protected int FindNextTagIndex(string[] splitArr, int index)
        {
            for (int i = index; i < splitArr.Length; i++)
            {

                //if (this.TagCharRegex.IsMatch(splitArr[i]))
                if (TagCharArray.Contains(splitArr[i]))
                {
                    return i;
                }
            }

            return 0;
        }


        protected int FindNextTagIndexByTag(string[] splitArr, int index, string findTag)
        {
            for (int i = index; i < splitArr.Length; i++)
            {
                if (splitArr[i] == findTag)
                {
                    return i;
                }
            }

            return 0;
        }

        private bool CheckArrLength(string[] arr, int index)
        {
            return arr.Length > index;
        }
    }

    public static class Extention
    {
        public static T TryGet<T>(this T[] arr, int index)
        {
            if (arr.Length <= index)
            {
                return default(T);
            }
            else
            {
                return arr[index];
            }
        }
    }

    public struct FindTagRangeResult
    {
        public string[] condition;
        public string tag;
        public int nextTagIndex;
    }
}
